package com.southgis.ibase.sql.func;

import com.southgis.ibase.sql.utils.SqlUtil;

import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;

public class OracleFunc extends CommonFunc
{
	@Override
	public String charType(int len)
	{
		if(len<0 || len>4000) {
			return getDialect().getTypeName(java.sql.Types.CLOB);
		}else if(len==0) {
			String typeName=getDialect().getTypeName(java.sql.Types.VARCHAR, 1, 0, 0);
			int pos=typeName.indexOf('(');
			if(pos>0) typeName=typeName.substring(0,pos).trim();
			return typeName;
		}else {
			return getDialect().getTypeName(java.sql.Types.VARCHAR, len, 0, 0);
		}
	}

	//标准写法
//	@Override
//	public String binaryType(int len)
//	{
//	}
	
	@Override
	public String datetimeType()
	{
		return "date";
	}
	@Override
	public String doubleType()
	{
		return "binary_double";
	}
	
	//标准写法
//	@Override
//	public String decimalType(int len,int scale)
//	{
//	}

	//标准写法
//	@Override
//	public String intType(int width)
//	{
//	}
	
	@Override
	public String notEmptyCharWhere(String fieldName)
	{
		return "(" + fieldName + " is not null)";
	}
	
	@Override
	public String emptyCharWhere(String fieldName)
	{
		return "(" + fieldName + " is null)";
	}

	@Override
	public String concatChar(String... charVals)
	{
		return "(" + String.join(" || ", charVals)+")";
	}

	@Override
	public String getCharToDateInvoke(String charVal, int fmt)
	{
		String sql="";
		switch(fmt)
		{
		case 0: //yyyy-MM-dd HH:mm:ss
			sql="to_date("+charVal+",'yyyy-mm-dd HH24:mi:ss')";
			break;
		case 1: //yyyyMMddHHmmss
			sql="to_date("+charVal+",'yyyymmddHH24miss')";
			break;
		case 10: //yyyy-MM-dd
			sql="to_date("+charVal+",'yyyy-mm-dd')";
			break;
		case 11: //yyyyMMdd
			sql="to_date("+charVal+",'yyyymmdd')";
			break;
		default://默认格式，即不用格式串
			sql=getCastInvoke(charVal,java.sql.Types.DATE,0,0);
			break;
		}

		return sql;
	}
	
	//标准写法
//	@Override
//	public String getYearInvoke(String dateVal)
//	{
//	}

	//标准写法
//	@Override
//	public String getDateConst(String fmtVal)
//	{
//	}

	//标准写法
//	@Override
//	public String getCastInvoke(String orgVal,int targetType,int length, int scale)
//	{
//	}
	
	@Override
	public String getNowInvoke()
	{
		return "SYSDATE";
	}
	
	@Override
	public String pageSql(String sql, int offset, int pageSize)
	{
		int max = offset + pageSize;

		return "select * from ( select row_.*, rownum rownum_ from (" + sql + ") row_ "
		 +" where rownum<=" + max + ") where rownum_>" + offset;
	}
	
	//标准写法
//	@Override
//	public String countSql(String sql)
//	{
//	}
	
	@Override
	public String sqlOfTableCount(String tableName, String schema)
	{
		return "SELECT COUNT(*) AS " + IExclusiveDatabaseFunc.ResultColumns.COUNT
				+ " FROM user_tab_comments " //ALL_TABLES
				+ " WHERE "
				//+" OWNER = '" + schema + "' AND "
				+ " upper(TABLE_NAME) = '" + tableName.toUpperCase() + "'";
	}
	
	@Override
	public String sqlOfListTable(String name, TableTypeEnum typeEnum, String schema)
	{
		StringBuilder sbSql=new StringBuilder();
		sbSql.append("SELECT TABLE_NAME AS ").append(ResultColumns.TABLE_NAME)
			.append(",COMMENTS AS ").append(ResultColumns.TABLE_COMMENT)
			.append(",CASE TABLE_TYPE WHEN 'VIEW' THEN 2 ELSE 1 END AS ").append(ResultColumns.TABLE_TYPE)
			.append(" FROM user_tab_comments ")//all_tab_comments
			.append(" WHERE ");
		//sbSql.append(" OWNER = '").append(schema).append("'");
		boolean needAnd=false;
		boolean needOr=false;
		final int type = typeEnum.getType();
		if(!StringUtils.isEmpty(name)) {
			if(needAnd) {
				sbSql.append(" AND ");
			}
			needAnd=true;
			sbSql.append(" upper(TABLE_NAME) ");
			boolean bescape=false;
			if((type&0x100)==0x100)
				sbSql.append(" = ");
			else {
				sbSql.append(" like ");
				if(name.indexOf('_')>=0) {
					name=name.replace("_", "\\_");
					bescape=true;
				}
			}
			sbSql.append("'").append(name.toUpperCase()).append("'");
			if(bescape) {
				sbSql.append(" ESCAPE '\\'");
			}
		}
		if(type!=0) {
			if(needAnd) {
				sbSql.append(" AND ");
			}
			needAnd=true;
			
			needOr=false;
			sbSql.append(" (");
			if((type&1)==1) {
				if(needOr) {
					sbSql.append(" OR ");
				}
				needOr=true;
				sbSql.append(" TABLE_TYPE='TABLE' ");
			}
			if((type&2)==2) {
				if(needOr) {
					sbSql.append(" OR ");
				}
				needOr=true;
				sbSql.append(" TABLE_TYPE='VIEW' ");
			}
			sbSql.append(")");
		}
		if(!needAnd) {
			sbSql.append(" 1=1 "); //在没有任何条件时，为了sql语法正确，添加一个总为真的条件（因为SQL串中已添加了where关键字）
		}
		return sbSql.toString();
	}

	@Override
	public String sqlOfListColumns(String tableName, String schema)
	{
		//设置查询类型：0不加条件，1 用等于，-1 用like
		int queryType=0;
		boolean bescape=false;
		if(!StringUtils.isEmpty(tableName)) {
			queryType=tableName.indexOf('%')<0?1:-1;
			if(queryType==-1 && tableName.indexOf('_')>=0) {
				tableName=tableName.replace("_", "\\_");
				bescape=true;
			}
		}
		return "SELECT tc.TABLE_NAME as " + ResultColumns.TABLE_NAME
				+ ",tc.COLUMN_NAME as " + ResultColumns.COLUMN_NAME
				+ ",cc.COMMENTS as " + ResultColumns.COLUMN_COMMENT
				+ ",tc.NULLABLE as " + ResultColumns.COLUMN_NULLABLE
				+ ",tc.DATA_TYPE as " + ResultColumns.DATA_TYPE
				+ ",CASE tc.CHAR_LENGTH WHEN 0 THEN NVL(tc.DATA_PRECISION,tc.DATA_LENGTH) ELSE tc.CHAR_LENGTH END as " + ResultColumns.DATA_LENGTH
				+ ",tc.DATA_SCALE as " + ResultColumns.DATA_SCALE
				+ ",NULL as " + ResultColumns.DATA_DEFINE
				+ " FROM USER_TAB_COLUMNS tc left join USER_COL_COMMENTS cc"
				+ " on tc.TABLE_NAME=cc.table_name and tc.COLUMN_NAME=cc.column_name"
				+ (queryType==0 ?
						"" :
						(" WHERE upper(tc.TABLE_NAME) "
							+(queryType==1 ? "='" : " like '")
							+ tableName.toUpperCase()
							+(bescape ? "' ESCAPE '\\'" : "'")
						)
				);
	}

	@Override
	public String sqlOfListConstraint(ConstraintTypeEnum typeEnum, String tableName,String schema)
	{
		StringBuilder sbSql=new StringBuilder();
		sbSql.append("SELECT uc.CONSTRAINT_NAME as ").append(ResultColumns.CONS_NAME)
				.append(",CASE uc.CONSTRAINT_TYPE WHEN 'P' THEN 1 WHEN 'U' THEN 2 WHEN 'R' THEN 4 ELSE 0 END as ").append(ResultColumns.CONS_TYPE)
				.append(",cc.COLUMN_NAME as ").append(ResultColumns.COLUMN_NAME)
				.append(" FROM USER_CONSTRAINTS uc left join USER_CONS_COLUMNS cc ")
				.append(" on uc.OWNER=cc.OWNER and uc.TABLE_NAME=cc.TABLE_NAME and uc.CONSTRAINT_NAME=cc.CONSTRAINT_NAME ")
				.append(" WHERE upper(uc.OWNER)='").append(schema.toUpperCase()).append("' AND ")
				.append(" upper(uc.TABLE_NAME) = '").append(tableName.toUpperCase()).append("'");
		boolean needAnd=true;
		boolean needOr=false;
		final int type = typeEnum.getType();
		//P  U  R
		if(type!=0) {
			if(needAnd) {
				sbSql.append(" AND ");
			}
			needAnd=true;
			
			needOr=false;
			sbSql.append("(");
			if((type&1)==1) {
				if(needOr) {
					sbSql.append(" OR ");
				}
				needOr=true;
				sbSql.append(" uc.CONSTRAINT_TYPE='P' "); //主键
			}
			if((type&2)==2) {
				if(needOr) {
					sbSql.append(" OR ");
				}
				needOr=true;
				sbSql.append(" uc.CONSTRAINT_TYPE='U' "); //唯一
			}
			if((type&4)==4) {
				if(needOr) {
					sbSql.append(" OR ");
				}
				needOr=true;
				sbSql.append(" uc.CONSTRAINT_TYPE='R' "); //外键
			}

			sbSql.append(")");
		}else {
			if(needAnd) {
				sbSql.append(" AND ");
			}
			needAnd=true;
			sbSql.append(" uc.CONSTRAINT_TYPE<>'C' "); //检查
		}
		return sbSql.toString();
	}
	
	@Override
	public List<String> sqlOfCreateTable(String tableName, String comment, List<ColumnDefine> colDef, String schema)
	{
		List<String> lsSql=new ArrayList<String>();
		StringBuilder sql=new StringBuilder();
		List<String> pkCol=new ArrayList<String>(colDef.size());
		
		lsSql.add("");//添加一个占位元素，最后在生成完create语句后，替换
		String fullTable=getFullTable(schema,tableName);
		sql.append("CREATE TABLE ").append(fullTable).append("(\n");
		for(int inx=0;inx<colDef.size();++inx) {
			ColumnDefine def=colDef.get(inx);
			if(inx>0) sql.append(",\n");
			sql.append(def.getName()).append(" ").append(getColumnTypeString(def));
			if(!def.isNullable()) {
				sql.append(" NOT NULL");
			}
			
			if(def.isPrimaryKey()) {
				pkCol.add(def.getName());
			}
			if(!StringUtils.isEmpty(def.getComment())) {
				lsSql.add(addColumnComment(fullTable,def.getName(),def.getComment()));
			}
		}
		if(pkCol.size()>0) {
			sql.append(",\n");
			sql.append("PRIMARY KEY (");
			sql.append(String.join(",", pkCol));
			sql.append(")");
		}
		sql.append("\n)");
		lsSql.set(0, sql.toString());
		
		if(!StringUtils.isEmpty(comment)) {
			lsSql.add(sqlOfModifyTableComment(comment,tableName,schema));
		}
		
		return lsSql;
	}
	
	@Override
	public String sqlOfModifyTableComment(String comment,String tableName,String schema)
	{
		return "COMMENT ON TABLE "+getFullTable(schema,tableName)
				+" is '"+SqlUtil.toSql(comment)+"'";
	}

	@Override
	public List<String> sqlOfAddColumn(ColumnDefine colDef,String tableName, String schema)
	{
		List<String> lsSql=new ArrayList<String>();
		String sql;
		String fullTable=getFullTable(schema,tableName);
		sql="ALTER TABLE "+fullTable
				+" ADD ("+colDef.getName()+" "+getColumnTypeString(colDef)+(colDef.isNullable()?" )":" NOT NULL)");
		lsSql.add(sql);
		
		if(!StringUtils.isEmpty(colDef.getComment())) {
			sql = addColumnComment(fullTable, colDef.getName(), colDef.getComment());
			lsSql.add(sql);
		}
		return lsSql;
	}
	protected String addColumnComment(String fullTable,String colName,String comment)
	{
		return "COMMENT ON COLUMN "+fullTable
				+"."+colName+" is '"+SqlUtil.toSql(comment)+"'";
	}
	
	@Override
	public List<String> sqlOfModifyColumn(ModifyColumnDefine colDef,String tableName, String schema)
	{
		List<String> lsSql=new ArrayList<String>();
		String sql;
		String fullTable=getFullTable(schema,tableName);
		
		if(colDef.isModifyName()) {
			sql="ALTER TABLE "+fullTable+" RENAME COLUMN "+colDef.getOldName()+" TO "+colDef.getName();
			lsSql.add(sql);
		}
		boolean setModifyNull=true;
		if(colDef.isModifyType()) {
			//以复制字段方式修改类型
			if(colDef.isByCopyModel())
			{
				String copyFiled=colDef.getName()+"_bak";
				//添加一个转换字段（如果存在这个字段时，会失败，需要用其他方式将这个备份字段删除）
				sql="ALTER TABLE "+fullTable + " ADD ("+copyFiled+" "+getColumnTypeString(colDef)+" )";
				lsSql.add(sql);
				//将数据复制到转换字段（此时已做了字段改名，所以不用考虑oldName的问题）
				sql=String.format("update %s set %s=%s", fullTable, copyFiled, colDef.getName());
				lsSql.add(sql);
				//删除原字段
				sql=sqlOfDropColumn(colDef.getName(), fullTable, null);
				lsSql.add(sql);
				//将转换字段改名为原字段名
				sql="ALTER TABLE "+fullTable+" RENAME COLUMN "+copyFiled+" TO "+colDef.getName();
				lsSql.add(sql);
			}else {
				setModifyNull=false;
				sql="ALTER TABLE "+fullTable+" MODIFY ("+colDef.getName()+" "+getColumnTypeString(colDef);
				if(colDef.isModifyNullable()) {
					sql=sql+" "+(colDef.isNullable()?"NULL":"NOT NULL");
				}
				sql=sql+")";
				lsSql.add(sql);
			}
		}

		if(setModifyNull && colDef.isModifyNullable()) {
			sql="ALTER TABLE "+fullTable+" MODIFY ("+colDef.getName()+" "+(colDef.isNullable()?"NULL":"NOT NULL")+")";
			lsSql.add(sql);
		}
		if(colDef.isModifyComment()) {
			sql = addColumnComment(fullTable, colDef.getName(), colDef.getComment());
			lsSql.add(sql);
		}
		return lsSql;
	}
	
	@Override
	public String sqlOfDropColumn(String colName, String tableName, String schema)
	{
		return String.format("alter table %s drop (%s)", getFullTable(schema,tableName),colName);
	}

	@Override
	public String sqlOfDropPrimaryKey(String keyName, String tableName, String schema)
	{
		return "ALTER TABLE "+getFullTable(schema,tableName)+" drop PRIMARY KEY CASCADE";
	}
	
	//标准写法
//	@Override
//	public String sqlOfAddUniqueKey(String keyName, String colName, String tableName, String schema)
//	{
//	}

	//标准写法
//	@Override
//	public String sqlOfDropUniqueKey(String keyName, String tableName, String schema)
//	{
//	}

	//标准写法
//	@Override
//	public String sqlOfDropForeignKey(String keyName, String tableName, String schema)
//	{
//	}
	
	@Override
	public String sqlOfEnableForeignKey(String keyName, Boolean enabled, String tableName, String schema)
	{
		//alter table {table} disable constraint {constraint}
		//alter table {table} enable constraint {constraint}
		return "ALTER TABLE "+getFullTable(schema,tableName)
				+" "+(enabled?"enable":"disable")+" constraint "+keyName;
	}

//	/**
//	 * 重载父类的实现，针对特殊类型特殊处理
//	 */
//	@Override
//	protected String getColumnTypeString(ColumnDefine colDef)
//	{
//		String typeStr=super.getColumnTypeString(colDef);
////		int len=colDef.getLength();
////		if(colDef.getType()==ColumnTypeEnum.Json)
////		{
////			if(len==0) len=2000;
////			typeStr=charType(len);
////		}
////		else if(colDef.getType()==ColumnTypeEnum.Geometry) {
////			//定义	 point（点）,line（两点直线段）,polyline（折线）,polygon（多边形），mpolygon（空心多边形，第一部分为外边界，剩下每一块描述的是内部空心边界）,rectangle（矩形）,circle（圆）
////			//暂时定义为二进制串
////			if(len==0) len=2000;
////			typeStr=binaryType(len);
////		}
//		return typeStr;
//	}

}
